package com.liuheqiao.sushegaunli.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "dormitory.db";
    private static final int DATABASE_VERSION = 4;

    // 通用列名
    public static final String COLUMN_ID = "id";
    public static final String COLUMN_BUILDING_NO = "building_no";
    public static final String COLUMN_ROOM_NO = "room_no";
    public static final String COLUMN_DESCRIPTION = "description";

    // 用户表
    public static final String TABLE_USERS = "users";
    public static final String COLUMN_USERNAME = "username";
    public static final String COLUMN_PASSWORD = "password";
    public static final String COLUMN_STUDENT_ID = "student_id";
    public static final String COLUMN_REAL_NAME = "real_name";
    public static final String COLUMN_PHONE = "phone";
    public static final String COLUMN_ROLE = "role";

    // 报修表
    public static final String TABLE_REPAIRS = "repairs";
    public static final String COLUMN_REPAIR_ID = "repair_id";
    public static final String COLUMN_USER_ID = "user_id";
    public static final String COLUMN_REPAIR_TYPE = "repair_type";
    public static final String COLUMN_STATUS = "status";
    public static final String COLUMN_CREATE_TIME = "create_time";
    public static final String COLUMN_UPDATE_TIME = "update_time";

    // 宿舍表
    public static final String TABLE_DORMITORIES = "dormitories";
    public static final String COLUMN_CAPACITY = "capacity";
    public static final String COLUMN_CURRENT_COUNT = "current_count";

    // 创建用户表的 SQL
    private static final String CREATE_TABLE_USERS = "CREATE TABLE " + TABLE_USERS + " (" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_USERNAME + " TEXT UNIQUE NOT NULL, " +
            COLUMN_PASSWORD + " TEXT NOT NULL, " +
            COLUMN_STUDENT_ID + " TEXT UNIQUE, " +
            COLUMN_REAL_NAME + " TEXT, " +
            COLUMN_PHONE + " TEXT, " +
            COLUMN_BUILDING_NO + " TEXT, " +
            COLUMN_ROOM_NO + " TEXT, " +
            COLUMN_ROLE + " TEXT DEFAULT 'student'" +
            ");";

    // 创建报修表的 SQL
    private static final String CREATE_TABLE_REPAIRS = "CREATE TABLE " + TABLE_REPAIRS + " (" +
            COLUMN_REPAIR_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_USER_ID + " INTEGER NOT NULL, " +
            COLUMN_BUILDING_NO + " TEXT NOT NULL, " +
            COLUMN_ROOM_NO + " TEXT NOT NULL, " +
            COLUMN_REPAIR_TYPE + " TEXT NOT NULL, " +
            COLUMN_DESCRIPTION + " TEXT, " +
            COLUMN_STATUS + " TEXT DEFAULT 'pending', " +
            COLUMN_CREATE_TIME + " DATETIME DEFAULT CURRENT_TIMESTAMP, " +
            COLUMN_UPDATE_TIME + " DATETIME DEFAULT CURRENT_TIMESTAMP, " +
            "FOREIGN KEY(" + COLUMN_USER_ID + ") REFERENCES " + TABLE_USERS + "(" + COLUMN_ID + ")" +
            ");";

    // 创建宿舍表的 SQL
    private static final String CREATE_TABLE_DORMITORIES = "CREATE TABLE " + TABLE_DORMITORIES + " (" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_BUILDING_NO + " TEXT NOT NULL, " +
            COLUMN_ROOM_NO + " TEXT NOT NULL, " +
            COLUMN_CAPACITY + " INTEGER NOT NULL, " +
            COLUMN_CURRENT_COUNT + " INTEGER DEFAULT 0, " +
            COLUMN_DESCRIPTION + " TEXT, " +
            "UNIQUE(" + COLUMN_BUILDING_NO + ", " + COLUMN_ROOM_NO + ")" +
            ");";

    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_USERS);
        db.execSQL(CREATE_TABLE_REPAIRS);
        db.execSQL(CREATE_TABLE_DORMITORIES);
        
        // 插入一些测试数据
        insertTestData(db);
    }

    private void insertTestData(SQLiteDatabase db) {
        // 插入默认管理员账号
        db.execSQL("INSERT INTO " + TABLE_USERS + 
            "(" + COLUMN_USERNAME + ", " + COLUMN_PASSWORD + ", " + 
            COLUMN_ROLE + ", " + COLUMN_REAL_NAME + ", " + 
            COLUMN_STUDENT_ID + ", " + COLUMN_PHONE + ", " +
            COLUMN_BUILDING_NO + ", " + COLUMN_ROOM_NO + ") " +
            "VALUES ('admin', 'admin', 'admin', '管理员', " +
            "'20240001', '13800138000', '1', '101');");
        
        // 插入一些测试宿舍
        db.execSQL("INSERT INTO " + TABLE_DORMITORIES + 
            "(" + COLUMN_BUILDING_NO + ", " + COLUMN_ROOM_NO + ", " + 
            COLUMN_CAPACITY + ", " + COLUMN_CURRENT_COUNT + ", " + 
            COLUMN_DESCRIPTION + ") " +
            "VALUES ('1', '101', 4, 1, '管理员宿舍');");
        
        db.execSQL("INSERT INTO " + TABLE_DORMITORIES + 
            "(" + COLUMN_BUILDING_NO + ", " + COLUMN_ROOM_NO + ", " + 
            COLUMN_CAPACITY + ", " + COLUMN_CURRENT_COUNT + ", " + 
            COLUMN_DESCRIPTION + ") " +
            "VALUES ('1', '102', 4, 0, '学生宿舍');");
        
        db.execSQL("INSERT INTO " + TABLE_DORMITORIES + 
            "(" + COLUMN_BUILDING_NO + ", " + COLUMN_ROOM_NO + ", " + 
            COLUMN_CAPACITY + ", " + COLUMN_CURRENT_COUNT + ", " + 
            COLUMN_DESCRIPTION + ") " +
            "VALUES ('1', '103', 4, 0, '学生宿舍');");
        
        db.execSQL("INSERT INTO " + TABLE_DORMITORIES + 
            "(" + COLUMN_BUILDING_NO + ", " + COLUMN_ROOM_NO + ", " + 
            COLUMN_CAPACITY + ", " + COLUMN_CURRENT_COUNT + ", " + 
            COLUMN_DESCRIPTION + ") " +
            "VALUES ('2', '101', 4, 0, '学生宿舍');");
        
        db.execSQL("INSERT INTO " + TABLE_DORMITORIES + 
            "(" + COLUMN_BUILDING_NO + ", " + COLUMN_ROOM_NO + ", " + 
            COLUMN_CAPACITY + ", " + COLUMN_CURRENT_COUNT + ", " + 
            COLUMN_DESCRIPTION + ") " +
            "VALUES ('2', '102', 4, 0, '学生宿舍');");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 2) {
            // 添加楼号字段
            db.execSQL("ALTER TABLE " + TABLE_USERS + 
                    " ADD COLUMN " + COLUMN_BUILDING_NO + " TEXT;");
            db.execSQL("ALTER TABLE " + TABLE_REPAIRS + 
                    " ADD COLUMN " + COLUMN_BUILDING_NO + " TEXT;");
        }
        
        if (oldVersion < 3) {
            // 创建宿舍表
            db.execSQL(CREATE_TABLE_DORMITORIES);
            
            // 插入测试数据
            db.execSQL("INSERT INTO " + TABLE_DORMITORIES + 
                "(" + COLUMN_BUILDING_NO + ", " + COLUMN_ROOM_NO + ", " + 
                COLUMN_CAPACITY + ", " + COLUMN_CURRENT_COUNT + ", " + 
                COLUMN_DESCRIPTION + ") " +
                "VALUES ('1', '101', 4, 1, '管理员宿舍');");
            
            db.execSQL("INSERT INTO " + TABLE_DORMITORIES + 
                "(" + COLUMN_BUILDING_NO + ", " + COLUMN_ROOM_NO + ", " + 
                COLUMN_CAPACITY + ", " + COLUMN_CURRENT_COUNT + ", " + 
                COLUMN_DESCRIPTION + ") " +
                "VALUES ('1', '102', 4, 0, '学生宿舍');");
            
            db.execSQL("INSERT INTO " + TABLE_DORMITORIES + 
                "(" + COLUMN_BUILDING_NO + ", " + COLUMN_ROOM_NO + ", " + 
                COLUMN_CAPACITY + ", " + COLUMN_CURRENT_COUNT + ", " + 
                COLUMN_DESCRIPTION + ") " +
                "VALUES ('2', '101', 4, 0, '学生宿舍');");
        }
    }
} 